"""
将本地保存的视频信息同步给数据库:
遍历本地文件：
    刷新is_exist为Y，is_has_been_exist为Y
遍历数据库
    如果本地视频不存在，刷新is_exist为N
"""
import shutil
import os
import mysql.connector

PATH = r'\\lcnas\xishu16T_2022\07-FC2\\'
MYSQL_DB = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="1234",
    database="ry-vue"
)


# 删除目录和目录下所有文件
def delete_folder_contents(folder):
    for filename in os.listdir(folder):
        file_path = os.path.join(folder, filename)
        try:
            if os.path.isfile(file_path) or os.path.islink(file_path):
                os.unlink(file_path)
            elif os.path.isdir(file_path):
                shutil.rmtree(file_path)
        except Exception as e:
            print(f'Failed to delete {file_path}. Reason: {e}')
    os.rmdir(folder)


def update_fc2(num, is_exist, is_has_been_exist):
    db_cursor = MYSQL_DB.cursor()
    sql = "UPDATE fc2_record SET is_exist = %s, is_has_been_exist = %s WHERE num = %s"
    val = (is_exist, is_has_been_exist, num)
    db_cursor.execute(sql, val)
    MYSQL_DB.commit()
    print('更新一条记录：{}'.format(num))


def query_fc2_by_num(num):
    db_cursor = MYSQL_DB.cursor()
    sql = "SELECT * FROM fc2_record WHERE num = '{}'".format(num)
    db_cursor.execute(sql)
    ans = db_cursor.fetchall()
    if len(ans) == 0:
        return False
    elif len(ans) == 1:
        return True
    else:
        print('【ERROR】查询到多条数据！！！！，num为：{}'.format(num))


def insert_fc2(query_num):
    db_cursor = MYSQL_DB.cursor()
    sql = "INSERT INTO fc2_record (num,name,tor,save_time,is_download,is_has_been_exist,is_exist,is_like) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
    val = (query_num, query_num, '', '2024-01-01 00:00:00', 'Y', 'Y', 'Y', 'N')
    db_cursor.execute(sql, val)
    MYSQL_DB.commit()
    print(val, '已插入')


def sync_fc2(path):
    root_dirs = os.listdir(path)
    for root_dir in root_dirs:

        query_num = root_dir.replace('FC2-', 'FC2-PPV-')
        if query_fc2_by_num(query_num):
            # 本地视频在数据库，刷新is_exist为Y，is_has_been_exist为Y
            update_fc2(query_num, 'Y', 'Y')
        else:
            # 本地视频不在数据库，新增一条记录
            insert_fc2(query_num)

        dirs = os.listdir(path + root_dir)
        # 如果是空目录，直接删除
        if len(dirs) == 0:
            os.rmdir(path + root_dir)
            # 刷新is_exist为N，is_has_been_exist为Y
            update_fc2(query_num, 'N', 'Y')
            print('删除空文件夹：' + path + root_dir)
        else:
            mp4_count = 0
            for d in dirs:
                suffix = d.split('.')[-1]
                if suffix == 'mp4':
                    mp4_count = mp4_count + 1
            # 如果没有mp4  直接删除
            if mp4_count == 0:
                delete_folder_contents(path + root_dir)
                # 刷新is_exist为N，is_has_been_exist为Y
                update_fc2(query_num, 'N', 'Y')
                print('删除没有mp4的文件夹：' + path + root_dir)


if __name__ == '__main__':
    sync_fc2(PATH)
